package work;

import work.utils.IdCardFormat;
import work.config.Constant;
import work.fram.JDBCConnect;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

//进行数据关联
public class Relevance {

    private static List<String> tables = new ArrayList<>();

    private static File moreIdCard = new File(Constant.work, "一个IdCard对应多条主表记录.txt");
    private static File nullIdCard = new File(Constant.work, "找不到IdCard对应的主表记录.txt");
    private static File handleR = new File(Constant.work, "处理身份证关联后.txt");

    private static Writer mW;
    private static Writer nW;
    private static Writer handleRW;


    static {

        String s = "tc_md_personnel_contract\n" +
                "tc_md_personnel_ducation\n" +
                "tc_md_personnel_family\n" +
                "tc_md_personnel_goabroad_situation\n" +
                "tc_md_personnel_linguistic_competence\n" +
                "tc_md_personnel_parttime_loan\n" +
                "tc_md_personnel_partygroups_record\n" +
                "tc_md_personnel_post_information\n" +
                "tc_md_personnel_practis_qualification\n" +
                "tc_md_personnel_punishment_situation\n" +
                "tc_md_personnel_resume_record\n" +
                "tc_md_personnel_reward_situation\n" +
                "tc_md_personnel_team_situation\n" +
                "tc_md_personnel_technical_position\n" +
                "tc_md_personnel_training_situation\n" +
                "tc_md_personnel_workers_technical_level";


        String[] split = s.split("\n");

        tables.addAll(Arrays.asList(split));

    }


    static {

        if (!moreIdCard.exists()) {
            try {
                moreIdCard.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        if (!nullIdCard.exists()) {
            try {
                nullIdCard.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        if (!handleR.exists()) {
            try {
                handleR.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        try {
            mW = new FileWriter(moreIdCard);
            nW = new FileWriter(nullIdCard);
            handleRW = new FileWriter(handleR);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public static void main(String[] args) throws SQLException, IOException {

        String regex = "^[1-9]\\d{5}(18|19|20|(3\\d))\\d{2}((0[1-9])|(1[0-2]))(([0-2][1-9])|10|20|30|31)\\d{3}[0-9Xx]$";

        Map<String, Node> nodeMap = new HashMap<>();

        //获取一个连接
        Statement statement = JDBCConnect.getStatement();

        ResultSet resultSet = statement.executeQuery("SELECT sysid,idcard FROM tc_md_personnel ");


        while (resultSet.next()) {

            String id = resultSet.getString("sysid");
            String idcard = resultSet.getString("idcard");

            if (!idcard.matches(regex)) {
                idcard = IdCardFormat.format(idcard);
            }

            idcard = idcard.toUpperCase();

            Node node = nodeMap.get(idcard);

            if (node == null) {
                node = new Node();
                nodeMap.put(idcard, node);
            }

            //放入id
            node.ids.add(id);
        }


        nodeMap.forEach((k, v) -> {
            if (v.ids.size() > 1) {
                System.out.println("重复身份证号:" + k);
            }
        });


        for (String table : tables) {

            resultSet = statement.executeQuery("SELECT id,idcard,name FROM " + table+" where person_id IS NULL");

            while (resultSet.next()) {
                String id = resultSet.getString("id");
                String idcard = resultSet.getString("idcard");
                String name = resultSet.getString("name");

                if (idcard == null) {
                    continue;
                }

                if (!idcard.matches(regex)) {
                    idcard = IdCardFormat.format(idcard);
                }

                idcard = idcard.toUpperCase();

                Node node = nodeMap.get(idcard);

                if (node == null) {

                    try {
                        nW.write("未找到身份证号对应记录:" + table + ":" + id + ":" + idcard + ":" + name + "\n");
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                    continue;
                }

                if (node.ids.size() > 1) {
                    try {
                        mW.write("多于1条记录:" + table + ":" + id + ":" + idcard + "\n");
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                } else {
                    handleRW.write("UPDATE " + table + " SET person_id=\"" + node.ids.get(0) + "\" WHERE id=\"" + id + "\";\n");
                }

            }

        }


        mW.flush();

        nW.flush();

        handleRW.flush();

    }

    private static class Node {
        List<String> ids = new ArrayList<>();
    }

}
